package job.Util;


import job.Driver;
import job.pojo.HoseholdUsers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class HoseholdDao {

    public ArrayList<HoseholdUsers> findAllHouseholdUsers() throws SQLException {
        String sql = "SELECT h.hotelname, h.hoteladdress, s.sportname, s.nationality, "
                + "s.intaketime, s.leavetime "
                + "FROM Sportsman s "
                + "JOIN HotelInfo h ON h.hotelid = s.hotelid";


        ArrayList<HoseholdUsers> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(new HoseholdUsers(
                        rs.getString("h.hotelname"),
                        rs.getString("h.hoteladdress"),
                        rs.getString("s.sportname"),
                        rs.getDate("s.intaketime"),
                        rs.getDate("s.leavetime")
                ));
            }
        }
        return list;
    }

    public ArrayList<HoseholdUsers> findHouseholdUsersByHotelName(String hotelName) throws SQLException {
        String sql = "SELECT h.hotelname, h.hoteladdress, s.sportname, s.nationality, "
                + "s.intaketime, s.leavetime "
                + "FROM Sportsman s "
                + "JOIN HotelInfo h ON h.hotelid = s.hotelid where hotelname=?";

        ArrayList<HoseholdUsers> list = new ArrayList<>();
        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, hotelName);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                list.add(new HoseholdUsers(
                        rs.getString("h.hotelname"),
                        rs.getString("h.hoteladdress"),
                        rs.getString("s.sportname"),
                        rs.getDate("s.intaketime"),
                        rs.getDate("s.leavetime")
                ));
            }
        }
        return list;
    }

    public ArrayList<String> findAllHotelNames() throws SQLException {
        String sql = "SELECT Hotelname FROM hotelinfo";
        ArrayList<String> list = new ArrayList<>();

        try (Connection conn = Driver.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(rs.getString("Hotelname"));
            }
        }
        return list;
    }

    public boolean addHouseholdUser(String hname, String uname, String froms, Date sdate) throws SQLException {
        try (Connection conn = Driver.getConnection()) {
            conn.setAutoCommit(false);  // 事务开始

            // 查询 hotel.id
            String hotelSql = "SELECT hotelid FROM hotelinfo where Hotelname = ?";
            int hid = -1;
            try (PreparedStatement ps = conn.prepareStatement(hotelSql)) {
                ps.setString(1, hname);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    hid = rs.getInt("hotelid");
                } else {
                    return false;
                }
            }


            // 插入 household
            String householdSql = "INSERT INTO Sportsman (sportname, nationality, hotelid, intaketime) VALUES (?,?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(householdSql)) {
                ps.setString(1,uname);
                ps.setString(2,froms);
                ps.setInt(3, hid);
                ps.setDate(4, new java.sql.Date(sdate.getTime()));
                ps.executeUpdate();
            }

            conn.commit();
            return true;
        }
    }
}
